<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0"
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
	xmlns:xs="http://www.w3.org/2001/XMLSchema"
	xmlns:fn="http://www.w3.org/2005/xpath-functions">
<!--
程序名:表结构生成工具
版本号    修改者    修改时间    修改描述
1.0       liuleyi   20141230    创建此程序
-->
	<xsl:output method="text" indent="no" encoding="utf-8" byte-order-mark="no"/>  
	<xsl:template match="/">
<xsl:for-each select="Tables">
	<xsl:variable name="MetadataTypeDefine" select="document('../model/0101_metadataTypeDefine.xml')"/>
<xsl:variable name="smallcase" select="'abcdefghijklmnopqrstuvwxyz'" /> 
<xsl:variable name="uppercase" select="'ABCDEFGHIJKLMNOPQRSTUVWXYZ'" /> 


<xsl:variable name="TableName" select="@code"/>
<xsl:variable name="fileName" select="concat('./generate/sql/pkg/pkg_',$TableName,'.sql')"/> 
<xsl:result-document encoding="utf-8" method="text" href="{$fileName}">
<xsl:variable name="TDTableXmlFile" select="concat('../model/tables/TDtable/',$TableName,'.xml')"/>
<xsl:variable name="TDTableXml" select="document($TDTableXmlFile)"/>
<xsl:for-each select="$TDTableXml/Table">
CREATE OR REPLACE PACKAGE Pkg_<xsl:value-of select="substring(lower-case($TableName),3)"/> IS

  PROCEDURE P_Insert(pi_fix Typ_fix_dml ,pi_Typ Typ_<xsl:value-of select="substring(lower-case($TableName),3)"/>);

  PROCEDURE P_Update(pi_fix Typ_fix_dml ,pi_Typ Typ_<xsl:value-of select="substring(lower-case($TableName),3)"/>);

  PROCEDURE P_Delete(pi_fix Typ_fix_dml ,pi_Typ Typ_<xsl:value-of select="substring(lower-case($TableName),3)"/>);
END;
/
CREATE OR REPLACE PACKAGE BODY Pkg_<xsl:value-of select="substring(lower-case($TableName),3)"/> IS

  PROCEDURE P_Insert(pi_fix Typ_fix_dml ,pi_Typ Typ_<xsl:value-of select="substring(lower-case($TableName),3)"/>) IS
    v_Errorcode Pkg_Type.Errorcode;
    v_Errormsg  Pkg_Type.Errormsg;
    v_Expcetion EXCEPTION;
    v_Updatedate      Pkg_Type.Updatedate;
    v_Updatetime      Pkg_Type.Updatetime;
    v_tablebeforestring Pkg_Type.tablebeforestring;
    v_tableafterstring  Pkg_Type.tableafterstring;
    v_Tablesting        Pkg_Type.Tablestring;
 
  BEGIN
    v_Updatedate      := Pkg_Tool.Getoperdate;
    v_Updatetime      := Pkg_Tool.Getopertime;

    INSERT INTO <xsl:value-of select="lower-case($TableName)"/>(<xsl:for-each select="Column">
                                                               <xsl:value-of select="@name"/><xsl:if test="position()!=last()">,</xsl:if>
</xsl:for-each>
                                                               ) 
                                                               VALUES(<xsl:for-each select="Column">
                                                               pi_Typ.<xsl:value-of select="@name"/><xsl:if test="position()!=last()">,</xsl:if>
</xsl:for-each>
                                                               );

    SELECT <xsl:for-each select="Column"><xsl:value-of select="@name"/><xsl:if test="position()!=last()">||'|'||</xsl:if></xsl:for-each> INTO v_tableafterstring
      FROM <xsl:value-of select="lower-case($TableName)"/> t <xsl:for-each select="Constraint"> 
<xsl:variable name="constraintName" select="@name"/>
<xsl:variable name="constraintSpace" select="@space"/>
<xsl:variable name="constraintType" select="@type"/>
<xsl:if test="$constraintType='primary'"><xsl:for-each select="Column">
     <xsl:if test="position() = 1 ">WHERE</xsl:if> t.<xsl:value-of select="lower-case(@name)"/> = pi_Typ.<xsl:value-of select="lower-case(@name)"/><xsl:if test="position()!=last()">  AND</xsl:if> 
</xsl:for-each>
  </xsl:if>
</xsl:for-each>;

    INSERT INTO t_lg_table_change (
                                   LCcode           ,
                                   opertimestamp    ,
                                   tablecode        ,
                                   tabledmltype     ,
                                   tablebeforestring,
                                   tableafterstring ,
                                   operdbobjid      ,
                                   updateoperid     ,
                                   updatedate       ,
                                   updatetime       
                                   ) VALUES(
                                   pi_fix.lccode,
                                   pi_fix.OperTimeStamp,
                                   '<xsl:value-of select="upper-case($TableName)"/>',
                                   'I',
                                   null,
                                   v_tableafterstring,
                                   pi_fix.dbobjid,
                                   pi_fix.operatorid,
                                   v_Updatedate,
                                   v_Updatetime
                                   );
  EXCEPTION

    WHEN OTHERS THEN
      ROLLBACK;
      v_Errorcode := SQLCODE;
      v_Errormsg  := SQLERRM;
      p_Lg_Raise_Error(Pi_Fix     => Typ_Fix_Para(Lccode=> Pi_Fix.Lccode,Operatorid=>Pi_Fix.Operatorid,Opertimestamp=>Pi_Fix.Opertimestamp,Paradesc=>null,Loginipaddress=>null,Loginmacaddress=>null),
                     Pi_Code    => v_Errorcode,
                     Pi_Msg     => v_Errormsg,
                     Pi_Dbobjid => Pkg_Tool.Getobjid);
      RAISE;
  END;

  PROCEDURE P_Update(pi_fix Typ_fix_dml,pi_Typ  Typ_<xsl:value-of select="substring(lower-case($TableName),3)"/>) IS
    v_Errorcode Pkg_Type.Errorcode;
    v_Errormsg  Pkg_Type.Errormsg;
    v_Expcetion EXCEPTION;  
    v_Updatedate      Pkg_Type.Updatedate;
    v_Updatetime      Pkg_Type.Updatetime;
    v_tablebeforestring Pkg_Type.tablebeforestring;
    v_tableafterstring  Pkg_Type.tableafterstring;
    v_Tablesting        Pkg_Type.Tablestring;
 
  BEGIN
    v_Updatedate      := Pkg_Tool.Getoperdate;
    v_Updatetime      := Pkg_Tool.Getopertime;
 
    SELECT <xsl:for-each select="Column"><xsl:value-of select="@name"/><xsl:if test="position()!=last()">||'|'||</xsl:if></xsl:for-each> INTO v_tablebeforestring
      FROM <xsl:value-of select="lower-case($TableName)"/> t <xsl:for-each select="Constraint"> 
<xsl:variable name="constraintName" select="@name"/>
<xsl:variable name="constraintSpace" select="@space"/>
<xsl:variable name="constraintType" select="@type"/>
<xsl:if test="$constraintType='primary'"><xsl:for-each select="Column">
     <xsl:if test="position() = 1 ">WHERE</xsl:if> t.<xsl:value-of select="lower-case(@name)"/> = pi_Typ.<xsl:value-of select="lower-case(@name)"/><xsl:if test="position()!=last()">  AND</xsl:if>
</xsl:for-each>
  </xsl:if>
</xsl:for-each>;

     UPDATE <xsl:value-of select="lower-case($TableName)"/> t set <xsl:for-each select="Column">t.<xsl:value-of select="@name"/> = <xsl:variable name="ColumnIsNull" select="@require"/><xsl:choose><xsl:when test="compare($ColumnIsNull,'Y')=0">nvl(pi_Typ.<xsl:value-of select="@name"/>,t.<xsl:value-of select="@name"/>)</xsl:when><xsl:when test="position() >= last()-2">nvl(pi_Typ.<xsl:value-of select="@name"/>,t.<xsl:value-of select="@name"/>)</xsl:when><xsl:otherwise>decode(pi_Typ.<xsl:value-of select="@name"/>,pkg_constant.SpeStringForUpNull,NULL,NULL,t.<xsl:value-of select="@name"/>,pi_Typ.<xsl:value-of select="@name"/>) </xsl:otherwise> </xsl:choose><xsl:if test="position()!=last()"> , </xsl:if></xsl:for-each>  <xsl:for-each select="Constraint"> 
<xsl:variable name="constraintName" select="@name"/>
<xsl:variable name="constraintSpace" select="@space"/>
<xsl:variable name="constraintType" select="@type"/>
<xsl:if test="$constraintType='primary'"><xsl:for-each select="Column">
     <xsl:if test="position() = 1 ">WHERE</xsl:if> t.<xsl:value-of select="lower-case(@name)"/> = pi_Typ.<xsl:value-of select="lower-case(@name)"/><xsl:if test="position()!=last()">  AND</xsl:if>
</xsl:for-each>
  </xsl:if>
</xsl:for-each>;

    SELECT <xsl:for-each select="Column"><xsl:value-of select="@name"/><xsl:if test="position()!=last()">||'|'||</xsl:if></xsl:for-each> INTO v_tableafterstring
      FROM <xsl:value-of select="lower-case($TableName)"/> t <xsl:for-each select="Constraint"> 
<xsl:variable name="constraintName" select="@name"/>
<xsl:variable name="constraintSpace" select="@space"/>
<xsl:variable name="constraintType" select="@type"/>
<xsl:if test="$constraintType='primary'"><xsl:for-each select="Column">
     <xsl:if test="position() = 1 ">WHERE</xsl:if> t.<xsl:value-of select="lower-case(@name)"/> = pi_Typ.<xsl:value-of select="lower-case(@name)"/><xsl:if test="position()!=last()">  AND</xsl:if>
</xsl:for-each>
  </xsl:if>
</xsl:for-each>;

    INSERT INTO t_lg_table_change (
                                   LCcode           ,
                                   opertimestamp    ,
                                   tablecode        ,
                                   tabledmltype     ,
                                   tablebeforestring,
                                   tableafterstring ,
                                   operdbobjid      ,
                                   updateoperid     ,
                                   updatedate       ,
                                   updatetime       
                                   ) VALUES(
                                   pi_fix.lccode,
                                   pi_fix.OperTimeStamp,
                                   '<xsl:value-of select="upper-case($TableName)"/>',
                                   'U',
                                   v_tablebeforestring,
                                   v_tableafterstring,
                                   pi_fix.dbobjid,
                                   pi_fix.operatorid,
                                   v_Updatedate,
                                   v_Updatetime
                                   );
  EXCEPTION

    WHEN OTHERS THEN
      ROLLBACK;
      v_Errorcode := SQLCODE;
      v_Errormsg  := SQLERRM;
      p_Lg_Raise_Error(Pi_Fix     => Typ_Fix_Para(Lccode=> Pi_Fix.Lccode,Operatorid=>Pi_Fix.Operatorid,Opertimestamp=>Pi_Fix.Opertimestamp,Paradesc=>null,Loginipaddress=>null,Loginmacaddress=>null),
                     Pi_Code    => v_Errorcode,
                     Pi_Msg     => v_Errormsg,
                     Pi_Dbobjid => Pkg_Tool.Getobjid);
      RAISE;
  END;

  PROCEDURE P_Delete(pi_fix Typ_fix_dml ,pi_Typ  Typ_<xsl:value-of select="substring(lower-case($TableName),3)"/>) IS
    v_Errorcode Pkg_Type.Errorcode;
    v_Errormsg  Pkg_Type.Errormsg;
    v_Expcetion EXCEPTION;
    v_Updatedate      Pkg_Type.Updatedate;
    v_Updatetime      Pkg_Type.Updatetime;
    v_tablebeforestring Pkg_Type.tablebeforestring;
    v_tableafterstring  Pkg_Type.tableafterstring;
    v_Tablesting        Pkg_Type.Tablestring;
  BEGIN
    v_Updatedate      := Pkg_Tool.Getoperdate;
    v_Updatetime      := Pkg_Tool.Getopertime;
    v_Tablesting      := <xsl:for-each select="Column">pi_Typ.<xsl:value-of select="@name"/><xsl:if test="position()!=last()">||'|'||</xsl:if></xsl:for-each>;
    SELECT <xsl:for-each select="Column"><xsl:value-of select="@name"/><xsl:if test="position()!=last()">||'|'||</xsl:if></xsl:for-each> INTO v_tablebeforestring
      FROM <xsl:value-of select="lower-case($TableName)"/> t <xsl:for-each select="Constraint"> 
<xsl:variable name="constraintName" select="@name"/>
<xsl:variable name="constraintSpace" select="@space"/>
<xsl:variable name="constraintType" select="@type"/>
<xsl:if test="$constraintType='primary'"><xsl:for-each select="Column">
     <xsl:if test="position() = 1 ">WHERE</xsl:if> t.<xsl:value-of select="lower-case(@name)"/> = pi_Typ.<xsl:value-of select="lower-case(@name)"/><xsl:if test="position()!=last()">  AND</xsl:if>
</xsl:for-each>
  </xsl:if>
</xsl:for-each>;

    DELETE <xsl:value-of select="lower-case($TableName)"/> t <xsl:for-each select="Constraint"> 
<xsl:variable name="constraintName" select="@name"/>
<xsl:variable name="constraintSpace" select="@space"/>
<xsl:variable name="constraintType" select="@type"/>
<xsl:if test="$constraintType='primary'"><xsl:for-each select="Column">
     <xsl:if test="position() = 1 ">WHERE</xsl:if> t.<xsl:value-of select="lower-case(@name)"/> = pi_Typ.<xsl:value-of select="lower-case(@name)"/><xsl:if test="position()!=last()">  AND</xsl:if>
</xsl:for-each>
  </xsl:if>
</xsl:for-each>;

    INSERT INTO t_lg_table_change (
                                   LCcode           ,
                                   opertimestamp    ,
                                   tablecode        ,
                                   tabledmltype     ,
                                   tablebeforestring,
                                   tableafterstring ,
                                   operdbobjid      ,
                                   updateoperid     ,
                                   updatedate       ,
                                   updatetime       
                                   ) VALUES(
                                   pi_fix.lccode,
                                   pi_fix.OperTimeStamp,
                                   '<xsl:value-of select="upper-case($TableName)"/>',
                                   'D',
                                   v_tablebeforestring,
                                   null,
                                   pi_fix.dbobjid,
                                   pi_fix.operatorid,
                                   v_Updatedate,
                                   v_Updatetime
                                   );
  EXCEPTION

    WHEN OTHERS THEN
      ROLLBACK;
      v_Errorcode := SQLCODE;
      v_Errormsg  := SQLERRM;
      p_Lg_Raise_Error(Pi_Fix     => Typ_Fix_Para(Lccode=> Pi_Fix.Lccode,Operatorid=>Pi_Fix.Operatorid,Opertimestamp=>Pi_Fix.Opertimestamp,Paradesc=>null,Loginipaddress=>null,Loginmacaddress=>null),
                     Pi_Code    => v_Errorcode,
                     Pi_Msg     => v_Errormsg,
                     Pi_Dbobjid => Pkg_Tool.Getobjid);
      RAISE;
  END;
END;
/
</xsl:for-each>
</xsl:result-document>
</xsl:for-each>

</xsl:for-each>
	</xsl:template>
</xsl:stylesheet>